﻿using System;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;
using System.Data.Common;
using System.Collections.Generic;
using System.Collections.Specialized;

using TExpert.Data;
using TExpert.Module;
using TExpert.Collections;
using TExpert.Interface;

namespace TExpert.SqlBase
{
    /// <summary>
    /// T模块Sql实现类。
    /// </summary>
    public class SqlBaseRepository<T> : IBaseRepository<T> where T : DbRepositoryProvider, new()
    {
        private T _t = null;
        private string _strPrimaryKey = "";
        private string _strTableName = "";
        #region Model层变量T的实例
        /// <summary>
        /// Model层变量Ｔ的实例
        /// </summary>
        private T t
        {
            get
            {
                if (_t == null)
                    _t = new T();
                return _t;
            }
        }
        #endregion
        #region 基本属性方法
        /// <summary>
        /// 要连接的数据库名
        /// </summary>
        public string strConName
        {
            get { return BaseAccessHelper.getConnName(t.DataBaseName); }
        }
        public string strConnect
        {
            get { return BaseAccessHelper.GetSqlConnectionString(t.DataBaseName); }
        }

        /// <summary>
        /// 主键字段名
        /// </summary>
        public string strPrimaryKey
        {
            get
            {
                if (_strPrimaryKey == "")
                    _strPrimaryKey = t.PrimaryKey;
                return _strPrimaryKey;
            }
            set { _strPrimaryKey = value; }
        }

        /// <summary>
        /// 数据表名
        /// </summary>
        public string strTableName
        {
            get
            {
                if (_strTableName == "")
                    _strTableName = "[" + typeof(T).Name + "]";
                return _strTableName;
            }
            set { _strTableName = value; }
        }
        #endregion

        #region T
        private DichotomyList<T> listCache;
        protected DichotomyList<T> ListCache
        {
            get { return listCache; }
        }

       
        #region GetModel
        /// <summary>
        /// 获得一个Model对象实例
        /// </summary>
        /// <param name="dr">一个数据行</param>
        /// <returns></returns>
        public virtual T GetModel(DataRow dr)
        {
            T model = new T();
            PropertyInfo[] pis = model.GetType().GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);

            foreach (PropertyInfo pi in pis)
            {
                try
                {
                    if (dr[pi.Name].ToString() != "")
                        pi.SetValue(model, dr[pi.Name], null);
                }
                catch (ArgumentException)
                { continue; }
            }
            return model;
        }

        /// <summary>
        /// 获得一个Model对象实例
        /// </summary>
        /// <param name="drv">一个数据行视图</param>
        public virtual T GetModel(DataRowView drv)
        {
            T model = new T();
            PropertyInfo[] pis = model.GetType().GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);

            foreach (PropertyInfo pi in pis)
            {
                try
                {
                    if (drv[pi.Name].ToString() != "")
                        pi.SetValue(model, drv[pi.Name], null);
                }
                catch (ArgumentException)
                { continue; }
            }
            return model;
        }

        /// <summary>
        /// 获得一个Model对象实例
        /// </summary>
        /// <param name="dr">一个IDataReader</param>
        /// <returns></returns>
        public virtual T GetModel(IDataReader dr)
        {
            T model = new T();
            PropertyInfo[] pis = model.GetType().GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);

            int intIndex;

            foreach (PropertyInfo pi in pis)
            {
                try
                {
                    intIndex = dr.GetOrdinal(pi.Name);
                }
                catch (IndexOutOfRangeException)
                { continue; }
                if (dr.IsDBNull(intIndex))
                    continue;
                pi.SetValue(model, dr.GetValue(intIndex), null);
            }
            return model;
        }
        #endregion GetModel

        #region Create
        /// <summary>
        /// 批量添加记录
        /// </summary>
        /// <param name="list">存储实体类的List</param>
        public virtual int Create(List<T> list)
        {
            return CreateList(list);
        }

        /// <summary>
        /// 批量添加记录
        /// </summary>
        /// <param name="dt">包含记录的DataTable</param>
        public virtual int Create(DataTable dt)
        {
            List<T> list = new List<T>();
            foreach (DataRowView drv in dt.DefaultView)
            {
                list.Create(GetModel(drv));
            }
            return CreateList(list);
        }

        /// <summary>
        /// 批量添加记录
        /// </summary>
        /// <param name="dr">包含记录的DataReader</param>
        public virtual int Create(IDataReader dr)
        {
            List<T> list = new List<T>();
            while (dr.Read())
            {
                list.Create(GetModel(dr));
            }
            return CreateList(list);
        }

        /// <summary>
        /// 添加单条记录，返回当前插入的这条记录的ID
        /// </summary>
        /// <param name="model">实体层某实体的实例</param>
        public virtual int Create(T model)
        {
            //PropertyInfo类：发现属性 (Property) 的属性 (Attribute) 并提供对属性 (Property) 元数据的访问。
            PropertyInfo[] pis = model.GetType().GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            SqlParameter[] parameters = new SqlParameter[pis.Length - 1];

            StringBuilder sbCloumnName = new StringBuilder();
            StringBuilder sbParameter = new StringBuilder();

            List<DataBaseParameter> listParam = new List<DataBaseParameter>();

            for (int i = 0; i <= parameters.Length; i++)
            {
                if (t.HasIdentityPK && t.PrimaryKey == pis[i].Name)
                    continue;

                sbCloumnName.AppendFormat("{0},", pis[i].Name);
                sbParameter.AppendFormat("@{0},", pis[i].Name);

                DataBaseParameter dbpm = new DataBaseParameter();
                dbpm.ParamName = pis[i].Name;
                dbpm.ParamDbType = BaseAccessHelper.GetDbType(pis[i].PropertyType);
                dbpm.ParamValue = pis[i].GetValue(model, null);
                listParam.Create(dbpm);
            }

            sbCloumnName = sbCloumnName.Replace(",", "", sbCloumnName.Length - 1, 1);
            sbParameter = sbParameter.Replace(",", "", sbParameter.Length - 1, 1);

            string strCommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2});SELECT IDENT_CURRENT('{0}')", strTableName, sbCloumnName, sbParameter);


            SqlParameter[] parms = new SqlParameter[listParam.Count];
            int k = 0;
            foreach (DataBaseParameter dbpm in listParam)
            {
                SqlParameter parm = new SqlParameter();
                parm.ParameterName = dbpm.ParamName;
                parm.DbType = dbpm.ParamDbType;
                parm.Value = dbpm.ParamValue;
                //parm.Size = 10;
                parms[k] = parm;
                k++;
            }
            object obj = SQLHelper.ExecuteScalar(strConnect, CommandType.Text, strCommandText, parms);            
            if (IsTableCache)
            {
                ListCache.Create(model);
            }

            return Convert.ToInt32(obj);
        }

        /// <summary>
        /// 批量添加记录 
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        internal int CreateList(List<T> list)
        {
            int intCount = 0;
            SqlConnection conn = new SqlConnection(strConnect);
            conn.Open();
            foreach (T model in list)
            {
                //PropertyInfo类：发现属性 (Property) 的属性 (Attribute) 并提供对属性 (Property) 元数据的访问。
                PropertyInfo[] pis = model.GetType().GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
                SqlParameter[] parameters = new SqlParameter[pis.Length - 1];

                StringBuilder sbCloumnName = new StringBuilder();
                StringBuilder sbParameter = new StringBuilder();

                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandType = CommandType.Text;

                for (int i = 0; i <= parameters.Length; i++)
                {
                    if (t.HasIdentityPK && t.PrimaryKey == pis[i].Name)
                        continue;

                    sbCloumnName.AppendFormat("{0},", pis[i].Name);
                    string strParameterName = string.Format("@{0}", pis[i].Name);
                    sbParameter.AppendFormat("{0},", strParameterName);

                    command.Parameters.Create(new SqlParameter(strParameterName, BaseAccessHelper.GetDbType(pis[i].PropertyType)));
                    command.Parameters[strParameterName].Value = pis[i].GetValue(model, null);
                }

                sbCloumnName = sbCloumnName.Replace(",", "", sbCloumnName.Length - 1, 1);
                sbParameter = sbParameter.Replace(",", "", sbParameter.Length - 1, 1);

                command.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", strTableName, sbCloumnName, sbParameter);

                if (command.ExecuteNonQuery() > 0)
                {
                    intCount++;
                    if (IsTableCache)
                    {
                        ListCache.Create(model);
                    }
                }
            }
            conn.Close();
            return intCount;
        }
        #endregion Create


        /// <summary>
        /// 更新T。
        /// </summary>
        public virtual bool UpdateArticle(T article)
        {
            DbConnection conn = DbHelper.CreateConnection();
            DbCommand cmd = DbHelper.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = string.Format("UPDATE {0}T SET Title=@Title,CategoryId=@CategoryId,Cover=@Cover,Color=@Color,Bold=@Bold,Author=@Author,ComeFrom=@ComeFrom,Clicked=@Clicked,CreateTime=@CreateTime,ModifyTime=@ModifyTime,Content=@Content,OrderBy=@OrderBy WHERE ArticleID=@ArticleID", TablePrefix);
            cmd.Parameters.Create(DbHelper.NewParam("@Title", DbType.String, 127, article.Title));
            cmd.Parameters.Create(DbHelper.NewParam("@CategoryId", DbType.Int32, article.CategoryId));
            cmd.Parameters.Create(DbHelper.NewParam("@Cover", DbType.String, 127, article.Cover));
            cmd.Parameters.Create(DbHelper.NewParam("@Color", DbType.String, 15, article.Color));
            cmd.Parameters.Create(DbHelper.NewParam("@Bold", DbType.Boolean, article.Bold));
            cmd.Parameters.Create(DbHelper.NewParam("@Author", DbType.String, 31, article.Author));
            cmd.Parameters.Create(DbHelper.NewParam("@ComeFrom", DbType.String, 127, article.ComeFrom));
            cmd.Parameters.Create(DbHelper.NewParam("@Clicked", DbType.Int32, article.Clicked));
            cmd.Parameters.Create(DbHelper.NewParam("@CreateTime", DbType.Date, article.CreateTime));
            cmd.Parameters.Create(DbHelper.NewParam("@ModifyTime", DbType.Date, article.ModifyTime));
            cmd.Parameters.Create(DbHelper.NewParam("@Content", DbType.String, article.Content));
            cmd.Parameters.Create(DbHelper.NewParam("@OrderBy", DbType.Int32, article.OrderBy));
            cmd.Parameters.Create(DbHelper.NewParam("@ArticleID", DbType.Int32, article.Id));

            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            bool result = val > 0;
            if (result) ListCache.Toggle(article);
            return result;
        }

        /// <summary>
        /// 删除T。
        /// </summary>
        public virtual bool DeleteArticle(T article)
        {
            DbConnection conn = DbHelper.CreateConnection();
            DbCommand cmd = DbHelper.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = string.Format("DELETE FROM {0}T WHERE ArticleID=@ArticleID", TablePrefix);
            cmd.Parameters.Create(DbHelper.NewParam("@ArticleID", DbType.Int32, article.Id));

            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            bool result = val > 0;
            if (result) ListCache.Remove(article.Id);
            return result;
        }

        /// <summary>
        /// 获取T实例。
        /// </summary>
        public virtual T GetArticle(Int32 id)
        {
            T article = null;
            //using(DataReader dr = new DataReader(DbHelper.ExecuteReader(string.Format("SELECT * FROM {0}T WHERE ArticleID=@ArticleID", TablePrefix),
            //        DbHelper.NewParam("@ArticleID", DbType.Int32, id))))
            //{
            //    if (dr.Read())
            //    {
            //        article = GetArticle(dr);
            //    }
            //}

            article = ListCache.GetOrDefault(id);
            return article;
        }

        /// <summary>
        /// 获得T列表。
        /// </summary>
        public virtual IEnumerable<T> GetArticles()
        {
            return ListCache;
        }

        /// <summary>
        /// 加载T列表。
        /// </summary>
        protected virtual IEnumerable<T> LoadArticles()
        {
            using (DataReader dr = new DataReader(DbHelper.ExecuteReader(string.Format("SELECT * FROM {0}T", TablePrefix))))
            {
                while (dr.Read())
                {
                    yield return GetArticle(dr);
                }
            }
        }

        /// <summary>
        /// 返回指定分类的文章。
        /// </summary>
        public virtual IEnumerable<T> GetArticles(Category category, int pageIndex, int pageSize, out int count)
        {
            string ids = category.Id == 0 ? string.Empty : "," + GetSelfAndChildrenString(category);
            var list = ListCache.Where(item => string.IsNullOrEmpty(ids) || (item.CategoryId.HasValue && ids.Contains(string.Format(",{0},", item.CategoryId.Value)))).ToList();
            count = list.Count;
            return list.Skip((pageIndex - 1) * pageSize).Take(pageSize);
        }

        /// <summary>
        /// 获取分类最新文章。
        /// </summary>
        public virtual IEnumerable<T> GetNewArticles(Category category, int top)
        {
            string path = "," + GetSelfAndChildrenString(category);

            return ListCache
                .Where(item => path.Contains("," + item.CategoryId + ","))
                .OrderByDescending(item => item.Id)
                .Take(top);
        }

        /// <summary>
        /// 获取分类推荐文章。
        /// </summary>
        public virtual IEnumerable<T> GetCommendArticles(Category category, int top)
        {
            string path = "," + GetSelfAndChildrenString(category);

            return ListCache
                .Where(item => path.Contains("," + item.CategoryId + ","))
                .OrderByDescending(item => item.OrderBy)
                .Take(top);
        }

        #endregion

        #region Category

        private DichotomyList<Category> categoryCache;
        protected DichotomyList<Category> CategoryCache
        {
            get { return categoryCache; }
        }

        /// <summary>
        /// 获得Category实例。
        /// </summary>
        protected virtual Category GetCategory(DataReader dr)
        {
            return new Category
            {
                Id = dr.GetValue<Int32>("CategoryID"),
                CategoryName = dr.GetValue<String>("CategoryName"),
                Color = dr.GetValue<String>("Color"),
                Bold = dr.GetValue<Boolean>("Bold"),
                ParentID = dr.GetValue<Int32>("ParentID"),
                OrderBy = dr.GetValue<Int32>("OrderBy")                
            };
        }

        /// <summary>
        /// 添加Category。
        /// </summary>
        public virtual bool CreateCategory(Category category)
        {
            DbConnection conn = DbHelper.CreateConnection();
            DbCommand cmd = DbHelper.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = string.Format("INSERT INTO {0}Category(CategoryName,Color,Bold,ParentID,OrderBy) VALUES(@CategoryName,@Color,@Bold,@ParentID,@OrderBy);SELECT SCOPE_IdENTITY();", TablePrefix);
            cmd.Parameters.Create(DbHelper.NewParam("@CategoryName", DbType.String, 63, category.CategoryName));
            cmd.Parameters.Create(DbHelper.NewParam("@Color", DbType.String, 15, category.Color));
            cmd.Parameters.Create(DbHelper.NewParam("@Bold", DbType.Boolean, category.Bold));
            cmd.Parameters.Create(DbHelper.NewParam("@ParentID", DbType.Int32, category.ParentID));
            cmd.Parameters.Create(DbHelper.NewParam("@OrderBy", DbType.Int32, category.OrderBy));

            category.Id = Convert.ToInt32(cmd.ExecuteScalar());
            cmd.Parameters.Clear();

            bool result = category.Id != default(Int32);
            if (result) CategoryCache.Create(category);
            return result;
        }

        /// <summary>
        /// 更新Category。
        /// </summary>
        public virtual bool UpdateCategory(Category category)
        {
            DbConnection conn = DbHelper.CreateConnection();
            DbCommand cmd = DbHelper.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = string.Format("UPDATE {0}Category SET CategoryName=@CategoryName,Color=@Color,Bold=@Bold,ParentID=@ParentID,OrderBy=@OrderBy WHERE CategoryID=@CategoryID", TablePrefix);
            cmd.Parameters.Create(DbHelper.NewParam("@CategoryName", DbType.String, 63, category.CategoryName));
            cmd.Parameters.Create(DbHelper.NewParam("@Color", DbType.String, 15, category.Color));
            cmd.Parameters.Create(DbHelper.NewParam("@Bold", DbType.Boolean, category.Bold));
            cmd.Parameters.Create(DbHelper.NewParam("@ParentID", DbType.Int32, category.ParentID));
            cmd.Parameters.Create(DbHelper.NewParam("@OrderBy", DbType.Int32, category.OrderBy));
            cmd.Parameters.Create(DbHelper.NewParam("@CategoryID", DbType.Int32, category.Id));

            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            bool result = val > 0;
            if (result) CategoryCache.Toggle(category);
            return result;
        }

        /// <summary>
        /// 删除Category。
        /// </summary>
        public virtual bool DeleteCategory(Category category)
        {
            DbConnection conn = DbHelper.CreateConnection();
            DbCommand cmd = DbHelper.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = string.Format("DELETE FROM {0}Category WHERE CategoryID=@CategoryID", TablePrefix);
            cmd.Parameters.Create(DbHelper.NewParam("@CategoryID", DbType.Int32, category.Id));

            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            bool result = val > 0;
            if (result) CategoryCache.Remove(category.Id);
            return result;
        }

        /// <summary>
        /// 获取Category实例。
        /// </summary>
        public virtual Category GetCategory(Int32 id)
        {
            Category category = null;
            //using(DataReader dr = new DataReader(DbHelper.ExecuteReader(string.Format("SELECT * FROM {0}Category WHERE CategoryID=@CategoryID", TablePrefix),
            //        DbHelper.NewParam("@CategoryID", DbType.Int32, id))))
            //{
            //    if (dr.Read())
            //    {
            //        category = GetCategory(dr);
            //    }
            //}

            category = CategoryCache.GetOrDefault(id);
            return category;
        }

        /// <summary>
        /// 获得Category列表。
        /// </summary>
        public virtual IEnumerable<Category> GetCategorys()
        {
            return CategoryCache;
        }

        /// <summary>
        /// 返回指定父id的所有分类。
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public virtual IEnumerable<Category> GetCategorys(int parentId)
        {
            return CategoryCache.Where(item => item.ParentID == parentId);
        }

        /// <summary>
        /// 加载Category列表。
        /// </summary>
        protected virtual IEnumerable<Category> LoadCategorys()
        {
            using (DataReader dr = new DataReader(DbHelper.ExecuteReader(string.Format("SELECT * FROM {0}Category", TablePrefix))))
            {
                while (dr.Read())
                {
                    yield return GetCategory(dr);
                }
            }
        }

        /// <summary>
        /// 获取自身及所有子类id以,号分格的字串。
        /// </summary>
        /// <param name="category"></param>
        /// <returns></returns>
        protected virtual string GetSelfAndChildrenString(Category category)
        {
            if (category.Id == 0)
                return string.Empty;

            var list = category.GetChildren();
            string result = category.Id + ",";
            
            foreach (var cat in list)
                result += GetSelfAndChildrenString(cat);

            return result;
        }

        #endregion

        #region Overrides

        public override void Initialize(string name, NameValueCollection config)
        {
            base.Initialize(name, config);

            listCache = DichotomyList<T>.Synchronized(new DichotomyList<T>(LoadArticles().ToList()));
            categoryCache = DichotomyList<Category>.Synchronized(new DichotomyList<Category>(LoadCategorys().ToList()));
        }

        #endregion

        #region [反]序列化
        /// <summary>
        /// 对象序列化
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public string Serialize(T model)
        {
            if (model == null)
                return string.Empty;

            IFormatter formatter = new BinaryFormatter();
            MemoryStream ms = new MemoryStream();
            formatter.Serialize(ms, model);
            ms.Seek(0, SeekOrigin.Begin);
            byte[] bt = new byte[ms.Length];
            ms.Read(bt, 0, bt.Length);
            ms.Close();
            return Convert.ToBase64String(bt);
        }

        /// <summary>
        /// 反序化为对象
        /// </summary>
        /// <param name="strBase64"></param>
        /// <returns></returns>
        public T Deserialize(string strBase64)
        {
            if (strBase64 == null)
                return null;
            T obj = null;
            try
            {
                IFormatter formatter = new BinaryFormatter();
                //string strTemp = Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(strBase64));

                byte[] bt = System.Convert.FromBase64String(strBase64);
                MemoryStream ms = new MemoryStream(bt);
                obj = formatter.Deserialize(ms) as T;
                ms.Close();
            }
            catch (Exception ex) { }
            return obj;
        }
        #endregion  序列化

    }
}
